package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.soda.bean.Course;
import com.soda.bean.Industry;
import com.soda.bean.News;
import com.soda.bean.Notice;
import com.soda.bean.Student;
import com.soda.bean.Survey;
import com.soda.bean.User;
import com.soda.dao.BaseDao;
import com.soda.dao.QianTaiCourseDao;

/**
 * 于安东
 */
public class QianTaiCourseDaoImpl extends BaseDao implements QianTaiCourseDao {

	/**
	 * 首页显示的在线课程(6条)
	 */
	public List<Course> selectZaiXianCourseNameByCouType(int type) {
		// TODO Auto-generated method stub

		try {
			getConnection();
			String sql = "select cou_name from course where cou_type = ? group by cou_name limit 0,6";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, type);
			res = pt.executeQuery();
			List<Course> names = new ArrayList<Course>();
			while (res.next()) {

				Course c = new Course();
				c.setCouName(res.getString("cou_name"));
				names.add(c);
			}
			return names;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	/**
	 * 按照分页查询课程 包括现场和在线的   还有搜索里面的模糊查询    和c.cou_state=3未下架
	 */
	public List<Course> selectZaiXianCourseByCouName(String couName, int page,
			int per, String couname, int type) {
		// TODO Auto-generated method stub
		getConnection();
		try {
			StringBuffer buff = new StringBuffer(
					"select c.cou_id,c.cou_name ,c.cou_opentime,c.cou_endtime,c.cou_money, u.use_name, c.cou_remark from course c INNER JOIN `user` u ON c.user_id=u.use_id "
							+ "where  c.cou_type=? and (c.cou_state=3 or c.cou_state=1)");

			if (couname == null || "".equals(couname)) {
				buff.append(" and c.cou_name = ? limit ?,?");
			}
			if (couname != null && !"".equals(couname)) {
				buff.append(" and c.cou_name like ? limit ?,?");
			}
			pt = conn.prepareStatement(buff.toString());

			if (couname == null || "".equals(couname)) {
				pt.setInt(1, type);
				pt.setString(2, couName);
				pt.setInt(3, (page - 1) * per);
				pt.setInt(4, per);
			}
			if (couname != null && !"".equals(couname)) {
				pt.setInt(1, type);
				pt.setString(2, "%" + couname + "%");
				pt.setInt(3, (page - 1) * per);
				pt.setInt(4, per);
			}
			res = pt.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while (res.next()) {
				Course c = new Course();
				c.setCouId(res.getInt("cou_id"));
				c.setCouOpentime(res.getString("cou_opentime"));
				c.setCouEndtime(res.getString("cou_endtime"));
				c.setCouName(res.getString("cou_name"));
				c.setCouMoney(res.getInt("cou_money"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				c.setUser(u);
				c.setCouRemark(res.getString("cou_remark"));
				list.add(c);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// 计算总条数
	/**
	 * 计算相同名字的在线视频的总条数
	 */
	public int getTotalCount(String couName, String couname, int couType) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer buf = new StringBuffer(
					"select count(1) from course where (cou_state = 1 or cou_state = 3) and cou_type=? ");
			if (couname == null || "".equals(couname)) {
				buf.append("and cou_name = ? ");
			}
			if (couname != null && !"".equals(couname)) {
				buf.append("and cou_name like ?");
			}
			pt = conn.prepareStatement(buf.toString());
			if (couname == null || "".equals(couname)) {
				pt.setInt(1, couType);
				pt.setString(2, couName);
			}
			if (couname != null && !"".equals(couname)) {
				pt.setInt(1, couType);
				pt.setString(2, "%" + couname + "%");
			}

			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public Course selectZaiXianShopingById(int id, int page, int per) {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select c.cou_type,c.cou_id,c.cou_name ,c.cou_money, u.use_name, c.cou_remark from course c INNER JOIN `user` u ON c.user_id=u.use_id where c.cou_id= ?  limit ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			pt.setInt(2, (page - 1) * per);
			pt.setInt(3, per);
			res = pt.executeQuery();

			if (res.next()) {
				Course c = new Course();
				c.setCouId(res.getInt("cou_id"));
				c.setCouType(res.getInt("cou_type"));
				c.setCouName(res.getString("cou_name"));
				c.setCouMoney(res.getInt("cou_money"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				c.setUser(u);
				c.setCouRemark(res.getString("cou_remark"));
				return c;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public List<News> selectNewsTitleByCouName() {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select new_id,new_title from news  limit 0,6";
			pt = conn.prepareStatement(sql);
			res = pt.executeQuery();
			List<News> list = new ArrayList<News>();
			while (res.next()) {
				News n = new News();
				n.setNewId(res.getInt("new_id"));
				n.setNewTitle(res.getString("new_title"));
				list.add(n);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public List<Industry> selectIndustryTitleByIndName() {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select ind_id,ind_title from industry  limit 0,6";
			pt = conn.prepareStatement(sql);
			res = pt.executeQuery();
			List<Industry> list = new ArrayList<Industry>();
			while (res.next()) {
				Industry n = new Industry();
				n.setIndId(res.getInt("ind_id"));
				n.setIndtitle(res.getString("ind_title"));
				list.add(n);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public List<Survey> selectSurveyContent() {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select sur_id,sur_content from survey ";
			pt = conn.prepareStatement(sql);
			res = pt.executeQuery();
			List<Survey> list = new ArrayList<Survey>();
			while (res.next()) {
				Survey sur = new Survey();
				sur.setSurId(res.getInt("sur_id"));
				sur.setSurContent(res.getString("sur_content"));
				list.add(sur);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public void updateCoustateByCouid(int couid, Student stu) {
		// TODO Auto-generated method stub
		try {
			conn = getConnection();
			String sql = "insert into stu_course values(?,?,0)";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, stu.getStuId());
			pt.setInt(2, couid);
			pt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

	}

	// 根据 学生的id 在stu_course表里面查询我的课程的id
	public List<Integer> getCouIdByStudentId(int stuId) {
		// TODO Auto-generated method stub
		conn = getConnection();
		String sql = "select cou_id from stu_course where stu_id = ?";
		try {
			pt = conn.prepareStatement(sql);
			pt.setInt(1, stuId);
			res = pt.executeQuery();
			List<Integer> list = new ArrayList<Integer>();
			while (res.next()) {
				list.add(res.getInt(1));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

	// 查询登陆的学生在他的未结算课程里面是否有他的为结算课程
	public List<Integer> selectStudentFromscCourseByStudent(Student stu) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select cou_id from stu_course where stu_id = ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, stu.getStuId());
			res = pt.executeQuery();
			List<Integer> ids = new ArrayList<Integer>();
			while (res.next()) {
				ids.add(res.getInt("cou_id"));
			}
			return ids;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// 若这个学生的课程里有课，判断这么课的状态 是否未结算 在线视频 0未结算 1已结算
	public int selectCourseStateByStuCourseId(int couId, int stuid) {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select sc_state as state from stu_course where cou_id = ? and stu_id = ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, couId);
			pt.setInt(2, stuid);
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt("state");
			} else {
				return -1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}

	// 查询未结算课程Id
	public Course selectInfoOfCourseBycouId(int couId) {
		// TODO Auto-generated method stub
		conn = getConnection();
		try {
			String sql = "select c.cou_name,u.use_name,c.cou_type,c.cou_money,c.cou_remark from course c inner join user u on c.user_id=u.use_id where c.cou_id = ? ";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, couId);
			res = pt.executeQuery();
			if (res.next()) {
				Course co = new Course();
				co.setCouName(res.getString("cou_name"));
				co.setCouMoney(res.getInt("cou_money"));
				co.setCouType(res.getInt("cou_type"));
				co.setCouRemark(res.getString("cou_remark"));
				User user = new User();
				user.setUseName(res.getString("use_name"));
				co.setUser(user);

				return co;

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// 查我的未结算课程
	public List<Course> selectCousFromStuCourseByStuId(int stuId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select c.cou_id,c.cou_name,u.use_name,c.cou_type,c.cou_money,c.cou_remark "
					+ "from course c inner join user u on c.user_id=u.use_id "
					+ "WHERE c.cou_id in (select cou_id from stu_course where stu_id = ? and sc_state =1 )";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, stuId);
			List<Course> list = new ArrayList<Course>();
			res = pt.executeQuery();
			while (res.next()) {
				Course co = new Course();
				co.setCouId(res.getInt("cou_id"));
				co.setCouName(res.getString("cou_name"));
				co.setCouType(res.getShort("cou_type"));
				co.setCouMoney(res.getInt("cou_money"));
				co.setCouRemark(res.getString("cou_remark"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				co.setUser(u);
				list.add(co);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	public void updateStuMoneyByStuI(int stuId, int getStuMoney) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "update  student  set stu_money =?  where stu_id =? ";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, getStuMoney);
			pt.setInt(2, stuId);
			pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}

	// 更改中间表中这门课这门课的状态为已购买
	public void updateCourseFromStuCourseStateByCouId(int parseInt) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "update  stu_course  set sc_state =1  where cou_id =? ";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, parseInt);
			pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

	}

	// 查询动态新闻内容
	public News selectNewseByNewsName(int newId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select new_id,new_title,new_author,new_content from news where new_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, newId);
			res = pt.executeQuery();
			if (res.next()) {
				News news = new News();
				news.setNewId(res.getInt("new_id"));
				news.setNewTitle(res.getString("new_title"));
				news.setNewAuthor(res.getString("new_author"));
				news.setNewContent(res.getString("new_content"));
				return news;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public Course getCourseInfoBycouName(int couid) {
		// TODO Auto-generated method stub
		getConnection();
		String sql = "select * from course c inner join user u on c.user_id = u.use_id where cou_id=?";
		try {
			pt = conn.prepareStatement(sql);
			pt.setInt(1, couid);
			res = pt.executeQuery();
			if (res.next()) {
				Course co = new Course();
				co.setCouName(res.getString("cou_name"));
				co.setCouType(res.getInt("cou_type"));
				co.setCouMoney(res.getInt("cou_money"));
				co.setCouState(res.getInt("cou_state"));
				co.setCouOpentime(res.getString("cou_opentime"));
				co.setCouEndtime(res.getString("cou_endtime"));
				co.setCouVideo(res.getString("cou_video"));
				co.setCouRemark(res.getString("cou_remark"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				co.setUser(u);
				return co;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	public void deleteInfoOfCourseBycouId(int couId) {
		// TODO Auto-generated method stub

		try {
			getConnection();
			String sql = "delete from stu_course where cou_id= ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, couId);
			pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

	}

	// 查询未结算课程总条数(包括搜索)
	public int getWeiJieCourseTotalCount(int stuId, String couname) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer buf = new StringBuffer(
					"SELECT count(1) FROM stu_course WHERE stu_id=? AND sc_state =0 ");

			if (couname != null) {
				buf.append("and cou_id in (select cou_id from course where cou_name like ? )");
			}
			pt = conn.prepareStatement(buf.toString());

			if (couname == null) {
				pt.setInt(1, stuId);
			} else if (couname != null) {
				pt.setInt(1, stuId);
				pt.setString(2, "%" + couname + "%");
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	// 查询我的课程总的条数 (包括搜索)
	public int selectCondiTotalCount(String couName, int stuId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer buf = new StringBuffer(
					"SELECT count(1) FROM stu_course WHERE stu_id=? AND sc_state =1  ");

			if (couName != null) {
				buf.append("and cou_id in (select cou_id from course where cou_name like ? )");
			}
			pt = conn.prepareStatement(buf.toString());

			if (couName == null) {
				pt.setInt(1, stuId);
			} else if (couName != null) {
				pt.setInt(1, stuId);
				pt.setString(2, "%" + couName + "%");
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}

	public int getTotalCountById(int id) {
		// TODO Auto-generated method stub
		getConnection();
		String sql = "select count(1) from course  where cou_id= ?  ";
		try {
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	// 带条件分页查询我的课程里面的课程(包括搜索)
	public List<Course> selectCourseOfBughtById(int stuId, String couName,
			int page, int per) {
		// TODO Auto-generated method stub

		try {
			getConnection();
			StringBuffer buf = new StringBuffer(
					"SELECT  c.cou_video,c.cou_id,c.cou_name,c.cou_type,u.use_name,c.cou_remark FROM course c inner JOIN `user` u ON  c.user_id=u.use_id"
							+ "  WHERE  c.cou_id in (select cou_id FROM stu_course WHERE stu_id=? AND sc_state = 1) ");

			if (couName != null) {
				buf.append("and c.cou_name like ? limit ?,?");
			} else {
				buf.append("limit ?,?");
			}
			pt = conn.prepareStatement(buf.toString());
			if (couName != null) {
				pt.setInt(1, stuId);
				pt.setString(2, "%" + couName + "%");
				pt.setInt(3, (page - 1) * per);
				pt.setInt(4, per);
			} else {
				pt.setInt(1, stuId);
				pt.setInt(2, (page - 1) * per);
				pt.setInt(3, per);
			}
			res = pt.executeQuery();
			List<Course> cos = new ArrayList<Course>();
			while (res.next()) {
				Course co = new Course();
				co.setCouId(res.getInt("cou_id"));
				co.setCouName(res.getString("cou_name"));
				co.setCouRemark(res.getString("cou_remark"));
				co.setCouVideo(res.getString("cou_video"));
				User u = new User();
				u.setUseName(res.getString("cou_name"));
				co.setUser(u);
				co.setCouType(res.getShort("cou_type"));
				cos.add(co);
			}
			return cos;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// *****查询未结算课程里面的课程(包括模糊)*****
	public List<Course> selectWeiJieSuanCourseByPage(int stuId, String couname,
			int page, int per) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer buff = new StringBuffer(
					"SELECT c.cou_video,c.cou_id,c.cou_money, c.cou_name,c.cou_type,u.use_name,c.cou_remark FROM course c inner JOIN `user` u ON  c.user_id=u.use_id"
							+ " WHERE  c.cou_id in (select cou_id FROM stu_course WHERE stu_id=? AND sc_state = 0) ");
			if (couname != null) {
				buff.append("and c.cou_name like ? limit ?,?");
			} else {
				buff.append("limit ?,?");
			}
			pt = conn.prepareStatement(buff.toString());
			if (couname != null) {
				pt.setInt(1, stuId);
				pt.setString(2, "%" + couname + "%");
				pt.setInt(3, (page - 1) * per);
				pt.setInt(4, per);
			} else {
				pt.setInt(1, stuId);
				pt.setInt(2, (page - 1) * per);
				pt.setInt(3, per);
			}
			res = pt.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while (res.next()) {
				Course co = new Course();
				co.setCouId(res.getInt("cou_id"));
				co.setCouVideo(res.getString("cou_video"));
				co.setCouName(res.getString("cou_name"));
				co.setCouType(res.getShort("cou_type"));
				co.setCouMoney(res.getInt("cou_money"));
				co.setCouRemark(res.getString("cou_remark"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				co.setUser(u);
				list.add(co);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// *****判断课程下架否*****
	public Course selectCourseByStucouId(int couId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select cou_id,cou_state,cou_name from course where cou_id = ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, couId);
			res = pt.executeQuery();
			if (res.next()) {
				Course co = new Course();
				co.setCouId(res.getInt("cou_id"));
				co.setCouState(res.getInt("cou_state"));
				co.setCouName(res.getString("cou_name"));
				return co;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	public Notice selectNoticeByNewsName(int notId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from notice where not_id = ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, notId);
			res = pt.executeQuery();
			if (res.next()) {
				Notice no = new Notice();
				no.setNotContent(res.getString("not_content"));
				no.setNotTitle(res.getString("not_title"));
				no.setNotUpdateTime(res.getString("not_updatetime"));
				return no;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

	public Industry selectIndustryByNewsName(int indId) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from industry where ind_id = ?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, indId);
			res = pt.executeQuery();
			if (res.next()) {
				Industry ind = new Industry(res.getInt(1), res.getString(2),
						res.getInt(3), res.getString(4), res.getString(5),
						res.getInt(6));
				return ind;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	// 查询现场课程 或者在线视频的课程总条数(包括模糊查询)
	public int getTotalCount(int coutype) {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select count(*) from course where cou_type = ? and (cou_state = 1 or cou_state = 3)";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, coutype);
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public List<Course> selectAllCourseByCouName(int parseInt, int page, int per) {
		// TODO Auto-generated method stub
		getConnection();
		try {
			String sql = "select c.cou_id,c.cou_name ,c.cou_opentime,c.cou_endtime,c.cou_money, u.use_name, c.cou_remark from course c INNER JOIN `user` u ON c.user_id=u.use_id "
					+ "where  c.cou_type=? and (c.cou_state=3 or c.cou_state=1) limit ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, parseInt);
			pt.setInt(2, (page - 1) * per);
			pt.setInt(3, per);
			res = pt.executeQuery();
			List<Course> list = new ArrayList<Course>();
			while (res.next()) {
				Course c = new Course();
				c.setCouId(res.getInt("cou_id"));
				c.setCouOpentime(res.getString("cou_opentime"));
				c.setCouEndtime(res.getString("cou_endtime"));
				c.setCouName(res.getString("cou_name"));
				c.setCouMoney(res.getInt("cou_money"));
				User u = new User();
				u.setUseName(res.getString("use_name"));
				c.setUser(u);
				c.setCouRemark(res.getString("cou_remark"));
				list.add(c);

			}
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
}
